<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/7/30 0030
 * Time: 上午 9:19
 */

namespace app\admin\controller;

header('Access-Control-Allow-Origin:*');
header('Access-Control-Allow-Methods:POST,GET');
header('Access-Control-Allow-Headers:x-requested-with,content-type');
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Controller;
use think\Db;
use think\Exception;
use \think\Validate;
define('DS', DIRECTORY_SEPARATOR);
defined('APP_PATH') or define('APP_PATH', dirname($_SERVER['SCRIPT_FILENAME']) . DS);
defined('ROOT_PATH') or define('ROOT_PATH', dirname(realpath(APP_PATH)) . DS);

/**
 * Class Cadre_relative 干部与亲属表
 * @package app\admin\controller
 * 此控制器关联表有
 * hb_cadre_relative （干部亲属表）
 * hb_department（部门表）
 * hb_cat（项目表）
 */
class Cadrerelative extends Authinfo
{
    //干部与亲属列表
    public function index_cadre(){
        $map=array();
        $uid=input('uid');
        //自定义超级管理员id为10
        if($uid!=10){
            $company_id=Db::name('Member')->where(['id'=>$uid])->value('company_id');
            $company_name=Db::name('Department')->where(['id'=>$company_id])->value('name');
            $map[]=array('company_id','=',$company_name);
        }else{
            $company_name=input('company_name');
            if($company_name){
                $map[]=array('company_id','=',$company_name);
            }
        }

//        $company_name=input('company_name');
//        if($company_name){
//            $map[]=array('company_id','=',$company_name);
//        }
        //模糊查询 证件号
        $sfid=input('sfid');
        if($sfid){
            $map[]=array('sfid','=',$sfid);
        }
        //干部姓名
        $name=input('name');
        if($name){
            $map[]=array('name','like','%'.$name.'%');
        }
        // 分页
        $current=input('current');
        if(!$current){
            $current=1;
        }
        $pagenums=input('pagenums');
        if(!$pagenums){
            $pagenums=50;
        }
        $nowpagenums=($current-1)*$pagenums;
        $order=input('order');
        if(!$order){
            $order='id DESC';
        }
        if($map){
            $total=Db::name('Cadre_relative')
                ->where($map)
                ->where('relationship','eq','本人')
                ->count('id');
            $info=Db::name('Cadre_relative')
                ->field('hb_cadre_relative.id,hb_cadre_relative.company_id,hb_cadre_relative.relationship,hb_cadre_relative.name,hb_cadre_relative.sex,hb_cadre_relative.sfid,hb_cadre_relative.mobile,hb_cadre_relative.job,hb_cadre_relative.rank,hb_cadre_relative.q_sfid')
                ->where($map)
                ->where('relationship','eq','本人')
                ->order($order)
                ->limit($nowpagenums,$pagenums)
                ->select();
//            dump($info);exit;
        }else{
            $total=Db::name('Cadre_relative')
                ->where('relationship','eq','本人')
                ->count('id');
            $info=Db::name('Cadre_relative')
                ->field('hb_cadre_relative.id,hb_cadre_relative.company_id,hb_cadre_relative.relationship,hb_cadre_relative.name,hb_cadre_relative.sex,hb_cadre_relative.sfid,hb_cadre_relative.mobile,hb_cadre_relative.job,hb_cadre_relative.rank,hb_cadre_relative.q_sfid')
                ->order($order)
                ->where('relationship','eq','本人')
                ->limit($nowpagenums,$pagenums)
                ->select();

        }
//        dump($info);


        if($info){
            //表头数量数据
            foreach($info as $k=>$v){
                //查询每个干部下亲属数量的条件是 身份证号等于干部的身份证号而且亲属关系不是本人
                $info[$k]['relatives_amount']=Db::name('Cadre_relative')
                    ->where('relationship','neq','本人')
                    ->where(['sfid'=>$v['sfid']])
                    ->count('id');
                //每个干部数量
                $info[$k]['cadres_amount']=Db::name('Cadre_relative')
                    ->where('relationship','eq','本人')
                    ->where(['sfid'=>$v['sfid']])
                    ->count('id');
                //子分类
                $info[$k]['son']=Db::name('Cadre_relative')
                    ->where('relationship','neq','本人')
                    ->where(['sfid'=>$v['sfid']])
                    ->select();
                    $sfids[]=$v['sfid'];
            }
            //头部亲属数量
            $relatives_num=Db::name('Cadre_relative')->where(['sfid'=>$sfids])->where('relationship','neq','本人')->count('q_sfid');
            //头部干部数量
            $cadre_num=Db::name('Cadre_relative')->where($map)->where('relationship','eq','本人')->count();

            $datas['status']=1;
            $datas['info']=$info;
            $datas['total']=$total;
            $datas['cadre_num']=$cadre_num;
            $datas['relatives_num']=$relatives_num;
            $datas['current']=$current;
        }else{
            $datas['status']=0;
            $datas['info']='无数据！';
        }
//        dump($datas);exit;
        return json($datas);
    }

    //干部与亲属的添加 出现亲属部分无限添加不同数据
    public function add_cadre(){
        //获取表单的全部数据
        $post=input('param.');
        //运用三元表达式判断表单值是否规范
        $new_array1['name']=!empty($post['b_name'])?$post['b_name']:'';
        $new_array1['sex']=!empty($post['sex'])?$post['sex']:'';
        $new_array1['sfid']=!empty($post['sfid'])?$post['sfid']:'';
        $new_array1['is_inspection']=!empty($post['is_inspection'])?$post['is_inspection']:'';
        $new_array1['polity']=!empty($post['polity'])?$post['polity']:'';
        $new_array1['rank']=!empty($post['rank'])?$post['rank']:'';
        $new_array1['company_id']=!empty($post['company_id'])?$post['company_id']:'';
        $new_array1['permanent']=!empty($post['permanent'])?$post['permanent']:'';
        $new_array1['job']=!empty($post['job'])?$post['job']:'';
        $new_array1['work']=!empty($post['work'])?$post['work']:'';
        $new_array1['content']=!empty($post['content'])?$post['content']:'';
        $new_array1['create_time']=time();
        $new_array1['mobile']=!empty($post['mobile'])?$post['mobile']:'';
        $new_array1['relationship']='本人';
        Db::startTrans();
        try {
            if(!empty($new_array1)){
                if(!empty($post['sfid'])){
                    $info=Db::name('Cadre_relative')
                        ->where(['relationship'=>'本人'])
                        ->where(['sfid'=>$post['sfid']])
                        ->find();
                    //存在
                    if(empty($info)){
                        //单独添加干部
                        Db::name('Cadre_relative')->insert($new_array1);
                        if(!empty($post['relatives'])){
                            $new_array=[];
                            foreach($post['relatives'] as $kv=>$vv){
                                //干部部分
                                $new_array[$kv]['name']=!empty($post['b_name'])?$post['b_name']:'';
                                $new_array[$kv]['sex']=!empty($post['sex'])?$post['sex']:'';
                                $new_array[$kv]['sfid']=!empty($post['sfid'])?$post['sfid']:'';
                                $new_array[$kv]['is_inspection']=!empty($post['is_inspection'])?$post['is_inspection']:'';
                                $new_array[$kv]['polity']=!empty($post['polity'])?$post['polity']:'';
                                $new_array[$kv]['rank']=!empty($post['rank'])?$post['rank']:'';
                                $new_array[$kv]['company_id']=!empty($post['company_id'])?$post['company_id']:'';
                                $new_array[$kv]['work']=!empty($post['work'])?$post['work']:'';
                                $new_array[$kv]['permanent']=!empty($post['permanent'])?$post['permanent']:'';
                                $new_array[$kv]['job']=!empty($post['job'])?$post['job']:'';
                                $new_array[$kv]['create_time']=time();
                                $new_array[$kv]['mobile']=!empty($post['mobile'])?$post['mobile']:'';
                                $new_array[$kv]['content']=!empty($post['content'])?$post['content']:'';
                                //亲属部分
                                $new_array[$kv]['q_name']=$vv['q_name'];
                                $new_array[$kv]['relationship']=$vv['relationship'];
                                $new_array[$kv]['q_sfid']=$vv['q_sfid'];
                                $new_array[$kv]['mobile']=$vv['mobile'];
                                $infos=Db::name('Cadre_relative')
                                    ->where(['q_sfid'=>$vv['q_sfid']])
                                    ->where(['sfid'=>$post['sfid']])
                                    ->find();
                                if($infos){
                                    Db::name('Cadre_relative')->where(['id'=>$infos['id']])->update($new_array[$kv]);
                                }else{
                                   Db::name('Cadre_relative')->insert($new_array[$kv]);
                                }
                            }
                        }
                    }else{
                        Db::name('Cadre_relative')->where(['id'=>$info['id']])->update($new_array1);
                        if(!empty($post['relatives'])){
                            $new_array=[];
                            foreach($post['relatives'] as $kv=>$vv){
                                //干部部分
                                $new_array[$kv]['name']=!empty($post['b_name'])?$post['b_name']:'';
                                $new_array[$kv]['sex']=!empty($post['sex'])?$post['sex']:'';
                                $new_array[$kv]['sfid']=!empty($post['sfid'])?$post['sfid']:'';
                                $new_array[$kv]['is_inspection']=!empty($post['is_inspection'])?$post['is_inspection']:'';
                                $new_array[$kv]['polity']=!empty($post['polity'])?$post['polity']:'';
                                $new_array[$kv]['rank']=!empty($post['rank'])?$post['rank']:'';
                                $new_array[$kv]['company_id']=!empty($post['company_id'])?$post['company_id']:'';
                                $new_array[$kv]['work']=!empty($post['work'])?$post['work']:'';
                                $new_array[$kv]['permanent']=!empty($post['permanent'])?$post['permanent']:'';
                                $new_array[$kv]['job']=!empty($post['job'])?$post['job']:'';
                                $new_array[$kv]['create_time']=time();
                                $new_array[$kv]['mobile']=!empty($post['mobile'])?$post['mobile']:'';
                                $new_array[$kv]['content']=!empty($post['content'])?$post['content']:'';
                                //亲属部分
                                $new_array[$kv]['q_name']=$vv['q_name'];
                                $new_array[$kv]['relationship']=$vv['relationship'];
                                $new_array[$kv]['q_sfid']=$vv['q_sfid'];
                                $new_array[$kv]['mobile']=$vv['mobile'];
                                $infos=Db::name('Cadre_relative')
                                    ->where(['q_sfid'=>$vv['q_sfid']])
                                    ->where(['sfid'=>$post['sfid']])
                                    ->find();
                                if($infos){
                                    Db::name('Cadre_relative')->where(['id'=>$infos['id']])->update($new_array[$kv]);
                                }else{
                                    Db::name('Cadre_relative')->insert($new_array[$kv]);
                                }
                            }
                        }
                    }
                }
            }
            Db::commit();
            return json(['status'=>1,'info'=>'新增成功']);
        } catch (Exception $e) {
            Db::rollback();
            return json(['status'=>0,'info'=>'新增失败']);
        }
    }
    //干部表修改数据回显
    public function update_view(){
        $id=input('id');
        $list=Db::name('Cadre_relative')
            ->where(['id'=>$id])->find();
        if($list){
            $datas['status']=1;
            $datas['info']=$list;
        }else{
            $datas['status']=0;
            $datas['info']='无数据!';
        }
        return json($datas);
    }

    //干部的编辑
    public function update_cadre(){
        $id=input('id');
        $datas=[];
        $data=[];
        $post=input('param.');
            if($id){
                $data['name']=!empty($post['name'])?$post['name']:'';
                $data['sex']=!empty($post['sex'])?$post['sex']:'';
                $data['job']=!empty($post['job'])?$post['job']:'';
                $data['is_inspection']=!empty($post['is_inspection'])?$post['is_inspection']:'';
                $data['work']=!empty($post['work'])?$post['work']:'';
                $data['sfid']=!empty($post['sfid'])?$post['sfid']:'';
                $data['polity']=!empty($post['polity'])?$post['polity']:'';
                $data['permanent']=!empty($post['permanent'])?$post['permanent']:'';
                $data['mobile']=!empty($post['mobile'])?$post['mobile']:'';
                $data['rank']=!empty($post['rank'])?$post['rank']:'';
                $data['company_id']=!empty($post['company_id'])?$post['company_id']:'';
                $data['relationship']=!empty($post['relationship'])?$post['relationship']:'';
                if($data){
                    $result=Db::name('Cadre_relative')->where(['id'=>$id])->update($data);
                    if($result){
                        $datas['status']=1;
                        $datas['info']='操作成功！';
                    }else{
                        $datas['status']=0;
                        $datas['info']='修改失败!';
                    }
                }else{
                    $datas['status']=0;
                    $datas['info']='无数据!';
                }

        }

        return json($datas);
    }

    //点击亲属数量展示亲属数据列表
    public function relatives_list(){
        $id=input('id');
        $current=input('current');
        if(!$current){
            $current=1;
        }
        $pagenums=input('pagenums');
        if(!$pagenums){
            $pagenums=50;
        }
        $nowpagenums=($current-1)*$pagenums;
        $order=input('order');
        if(!$order){
            $order='id DESC';
        }
        if($id){
            //查询亲属数量的条件是 身份证号等于干部的身份证号而且亲属关系不是本人
            $sfid=Db::name('Cadre_relative')->where(['id'=>$id])->field('sfid')->find();
            $info=Db::name('Cadre_relative')
                ->order($order)
                ->where('relationship','eq','本人')
                ->where(['sfid'=>$sfid])
                ->limit($nowpagenums,$pagenums)
                ->select();
            foreach($info as $key=>$value){
                //子分类
                $info[$key]['son']=Db::name('Cadre_relative')
                    ->where('relationship','neq','本人')
                    ->where(['sfid'=>$value['sfid']])
                    ->select();
                //查询亲属数量的条件是 身份证号等于干部的身份证号而且亲属关系不是本人
                $info[$key]['relative_amount']=Db::name('Cadre_relative')
                    ->where('relationship','neq','本人')
                    ->where(['sfid'=>$value['sfid']])
                    ->count('id');
            }
            if($info){
                $datas['status']=1;
                $datas['info']=$info;
            }else{
                $datas['status']=0;
                $datas['info']='无数据！';
            }

        }else{
            $datas['status']=0;
            $datas['info']='无参数！';
        }
        return json($datas);
    }

    //亲属表数据回显
    public function relative_view(){
        $id=input('id');
        $list=Db::name('Cadre_relative')
            ->field('id,q_name,q_sfid,sex,polity,mobile,job,company_id,relationship')
            ->where(['id'=>$id])->find();
        if($list){
            $datas['status']=1;
            $datas['info']=$list;
        }else{
            $datas['status']=0;
            $datas['info']='无数据!';
        }
        return json($datas);
    }

    //亲属的编辑
    public function update_relative(){
        $id=input('id');
        $post=input('param.');
        $datas=[];
        $data=[];
            if($id){
                $data['q_name']=!empty($post['q_name'])?$post['q_name']:'';
                $data['sex']=!empty($post['sex'])?$post['sex']:'';
                $data['q_sfid']=!empty($post['q_sfid'])?$post['q_sfid']:'';
                $data['polity']=!empty($post['polity'])?$post['polity']:'';
                $data['mobile']=!empty($post['mobile'])?$post['mobile']:'';
                $data['job']=!empty($post['job'])?$post['job']:'';
                $data['company_id']=!empty($post['company_id'])?$post['company_id']:'';
                $data['relationship']=!empty($post['relationship'])?$post['relationship']:'';

                if($data){
                    $result=Db::name('Cadre_relative')->where(['id'=>$id])->update($data);
                    if($result){
                        $datas['status']=1;
                        $datas['info']='操作成功！';
                    }else{
                        $datas['status']=0;
                        $datas['info']='修改失败!';
                    }
                }else{
                    $datas['status']=0;
                    $datas['info']='无数据!';
                }

            }else{
                $datas['status']=0;
                $datas['info']='参数缺失!';
            }

        return json($datas);
    }

    //亲属表的多个删除
    public function relatives_delete(){
        $idArray=input('id');
            if(is_array($idArray)){
                $result = Db::name('Cadre_relative')
                    ->where('id', 'in', $idArray)
                    ->delete();
                if($result){
                    $datas['status'] = 1;
                    $datas['info'] = '删除成功';
                }else{
                    $datas['status'] = 0;
                    $datas['info'] = '删除失败！';
                }
            }else{
                $datas['status']=0;
                $datas['info']='无参数！';
            }


        return json($datas);
    }
    //列表页删除
    public function cadre_delete(){
        $sfids=input('sfid');
        if(is_array($sfids)){
                $result=Db::name('Cadre_relative')->where('sfid','in',$sfids)->delete();
            if($result){
                $datas['status']=1;
                $datas['info']='删除成功！';
            }else{
                $datas['status']=0;
                $datas['info']='删除失败！';
            }
        }else{
            $datas['status']=0;
            $datas['info']='无参数！';
        }
        return json($datas);
    }

    //部门分类列表
    public function index_department(){
        //通过用户id得到部门id 查看本人相关的部门
        $uid=input('uid');
        $map=[];
        //自定义超级管理员id为10
        if($uid!=10){
            $company_id=Db::name('Member')->where(['id'=>$uid])->value('company_id');
            $map[]=array('id','=',$company_id);
        }
        $order = input('order');
        if($order){
            $order='id DESC';
        }// 排序
        if($map){
            $infos=Db::name('Department')->where('pid',0)->where($map)->field('id,pid,name')->order($order)->select();

        }else{
            $infos=Db::name('Department')->where('pid',0)->field('id,pid,name')->order($order)->select();

        }
        foreach($infos as $k => $v){
            $infos[$k]['son']=Db::name('Department')->where('pid',$v['id'])->field('id,pid,name')->order($order)->select();
        }

        if($infos){
            $datas['status']=1;
            $datas['info']=$infos;
        }else{
            $datas['status']=0;
            $datas['info']='无数据！';
        }
        return json($datas);
    }

    //干部&亲属数据批量导入
    public function pladd(){
        $pathurl=input('pathurl');
//        $pathurl='uploads\wenjian\20180619\20180801.xlsx';
        $startRow=input('startRow');
        if(!$startRow){
            $startRow=2;
        }
        if($pathurl){
            $lujing=$pathurl;
            $excelFile=ROOT_PATH.$lujing;
            $excelReader =IOFactory::createReader('Xlsx');
            $excelReader->setReadDataOnly(true);
            $endRow = input('endRow');
            if (!$endRow) {
                $endRow = 2000;
            }
            $filterSubset=new Ireadefilter($startRow, $endRow,range('A', 'M'));
            $excelReader->setReadFilter($filterSubset);
            $phpexcel    = $excelReader->load($excelFile);
            $activeSheet = $phpexcel->getActiveSheet();
            $zongshu=$activeSheet->getHighestRow(); //总行数
            if($endRow>$zongshu){
                $endRow=$zongshu;
            }
            if($startRow<=$zongshu){
                $sheetData = $activeSheet->toArray(null, true, true, true);
                $highestColumn      = $activeSheet->getHighestColumn(); //最后列数所对应的字母，例如第2行就是B
                $data = array();
                for ($col = $startRow; $col <=$endRow; $col++) {
                    if($sheetData[$col]){
                        $data[] = $sheetData[$col];
                    }
                }
                $phpexcel->disconnectWorksheets();
                unset($phpexcel);
                if($data){
                    $nums=0;
                    foreach ($data as $key => $value){
                        $shujudata=array();
                        $shujudata['name']=$value['A'];
                        $shujudata['sfid']=$this->NumToStr($value['B']);
                        $shujudata['relationship']=$value['C'];
                        if($value['C']=='本人'){
                            $shujudata['job']=$value['H'];
                        }else{
                            $shujudata['job']='';
                        }
                        $shujudata['q_name']=$value['D'];
                        $shujudata['sex']=$value['E'];
                        $shujudata['q_sfid']=$this->NumToStr($value['F']);
                        $shujudata['company_id']=$value['G'];
                        $shujudata['job']=$value['H'];
                        $shujudata['rank']=$value['I'];
                        $shujudata['polity']=$value['J'];
                        if(array_key_exists('J', $value)) {
                            $shujudata['mobile']=$value['K'];
                        }else{
                            $shujudata['mobile']='';
                        }
                        $shujudata['permanent']=$value['L'];
                        $shujudata['address']=$value['M'];

                        if($value['B']){//干部sfid存在
                            //查询是否存在数据库中
                            $cz=Db::name('Cadre_relative')->where('sfid',$value['B'])->find();
                            if(!$cz){
                                //不存在则进行添加操作
                               $shujudata['create_time']=time();
                                $idss=Db::name('Cadre_relative')->insert($shujudata);

                                if($idss){
                                    $nums++;
                                    unset($shujudata);
                                }
                                //判断是干部id且亲属关系不是本人===》添加亲属
                            }else if($value['B']&&$value['C']!='本人'){
                                //当亲属sfid存在
                                if($value['F']){
                                    //查看是否存在亲属信息
                                    $info=Db::name('Cadre_relative')->where('q_sfid',$value['F'])->where('relationship','<>','本人')->find();

                                    if(!$info){//不存在则进行添加操作
                                        $shujudata['create_time']=time();
                                        $idss=Db::name('Cadre_relative')->insert($shujudata);
                                        if($idss){
                                            $nums++;
                                            unset($shujudata);
                                        }
                                    }else{//存在则进行重复数据统一操作
                                        $shujudata['create_time']=time();
                                        $gx=Db::name('Cadre_relative')->where('id',$info['id'])->update($shujudata);
                                        $nums++;
                                        unset($shujudata);
                                    }
                                }
                            }else{
                                $shujudata['create_time']=time();
                                $gx=Db::name('Cadre_relative')->where('id',$cz['id'])->update($shujudata);
                                $nums++;
                                unset($shujudata);
                            }
                            unset($cz);
                        }
                    }

                    $datas['status']=1;
                    $tiaoshu=$endRow;

                    $datas['cginfo']=$nums;
                    $datas['nextnums']=$endRow+1;
                    $datas['isend']=0;
                    unset($data);

                }else{
                    $datas['status']=0;
                    $datas['info']='无数据！';

                }

            }else{
                $datas['status']=1;
                $datas['info']='导入完毕';
                $datas['isend']=1;

            }

        }else{
            $datas['status']=0;
            $datas['info']='文件不存在';


        }
        return  json($datas);

    }

    //将科学计数法转换为原始数字字符串的方法
    function NumToStr($num){
        if (stripos($num,'e')===false) return $num;
        $num = trim(preg_replace('/[=\'"]/','',$num,1),'"');//出现科学计数法，还原成字符串
        $result = "";
        while ($num > 0){
            $v = $num - floor($num / 10)*10;
            $num = floor($num / 10);
            $result = $v . $result;
        }
        return $result;
    }

}